package com.ftg.learn.day15.myframework.util;

import lombok.*;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 通用处理部分
 *
 * @author 布衣
 */
@Data
@NoArgsConstructor
@AllArgsConstructor
public class JdbcUtil {

    private String username;
    private String password;
    private String url;

    /**
     * sql语句当中全是常量，没有变量  ? 静态sql statement
     * 如  select * from where deptno = 123;
     * 预编译sql语句  PreparedStatement
     * select * from dept where depnto = ?;
     *
     * @param sql
     * @return
     */
    public List<Map<String, Object>> query(String sql, Object... obj) {
        List<Map<String, Object>> list = new ArrayList<>();
        try (
                Connection conn = DriverManager.getConnection(this.getUrl(), this.getUsername(), this.getPassword());
                PreparedStatement statement = conn.prepareStatement(sql);
        ) {

            if (null != obj) {
                for (int i = 0; i < obj.length; i++) {
                    statement.setObject(i + 1, obj[i]);
                }
            }

            //结果集里面有几列？ 列名叫什么？
            ResultSet rs = statement.executeQuery();
            //结果集元信息
            ResultSetMetaData metaData = rs.getMetaData();

            while (rs.next()) {
                Map<String, Object> ones = new HashMap<>();
                for (int i = 0; i < metaData.getColumnCount(); i++) {
                    ones.put(metaData.getColumnLabel(i + 1).toLowerCase(), rs.getObject(i + 1));
                }
                list.add(ones);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    /**
     * 其它操作  添加、修改、删除
     * @param sql  语句
     * @param obj  参数
     * @return
     */
    public int dml(String sql, Object... obj) {
        try (
                Connection conn = DriverManager.getConnection(this.getUrl(), this.getUsername(), this.getPassword());
                PreparedStatement statement = conn.prepareStatement(sql);
        ) {

            if (null != obj) {
                for (int i = 0; i < obj.length; i++) {
                    statement.setObject(i + 1, obj[i]);
                }
            }
            return statement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return -1;
    }

    @SneakyThrows
    public static void main(String[] args) {
        JdbcUtil jdbcUtil = new JdbcUtil("root", "123456", "jdbc:mysql://localhost:3306/db2402");
        String sql = "select dept.deptno id,dname from dept where dname like concat(?,'%')";
        List<Map<String, Object>> query = jdbcUtil.query(sql,"R");
        System.out.println(query);
    }

}
